---------------------------------------------------------------
-- Session 2 Asynch/Service Broker/Query Notifications
---------------------------------------------------------------

---------------------------------------------------------------
-- SB Setup

--------------------------------------------------------------------
-- Summary: Setup script for Service Broker HelloWorld sample.
-- 
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------

-- This script sets up a pair of simple Service Broker
-- services.

-- The TargetService supports the contract HelloWorldContract. This
-- contract allows either side of the conversation to send a
-- message of type HelloWorldMessage. A HelloWorldMessage can
-- be any well-formed XML.


--------------------------------------------------------------------
-- Activate Service Broker message delivery in AdventureWorks.

-- Activate Service Broker message delivery in AdventureWorks. A Beta 2
-- limitation of the setup procedure deactivates message delivery during installation.

use master ;
GO

-- If Service Broker message delivery is not active in AdventureWorks, alter the
-- database to make message delivery active.

SET NOCOUNT ON ;
GO

IF NOT EXISTS
  (SELECT * FROM sys.databases
   WHERE name = 'AdventureWorks'
   AND is_broker_enabled = 1)
BEGIN
  ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
END ;
GO 
---------------------------------------------------------------------

USE AdventureWorks ;
GO

--------------------------------------------------------------------
-- Remove any existing objects for the sample.
--------------------------------------------------------------------

-- Notice that the order for dropping objects is different
-- than the order for creating objects. Because contracts depend
-- on message types, a contract must be created after the 
-- message types that contract uses, and dropped before the
-- message types. A service is generally created after the contracts


-- Because services depend on contracts and queues,
-- the script drops services before dropping contracts
-- or queues.

-- Drop InitiatorService if the service exists.

IF EXISTS (SELECT *
           FROM sys.services
           WHERE name = 'InitiatorService')
BEGIN
    DROP SERVICE InitiatorService ;
END ;
GO

-- Drop TargetService if the service exists.

IF EXISTS (SELECT *
           FROM sys.services
           WHERE name = 'TargetService')
BEGIN
    DROP SERVICE TargetService ;
END ;
GO

-- Because contracts depend on message types, the script
-- drops contracts before dropping message types.

-- Drop HelloWorldContract if the contract exists.

IF EXISTS (SELECT *
           FROM sys.service_contracts
           WHERE name = 'HelloWorldContract')
BEGIN
    DROP CONTRACT HelloWorldContract ;
END ;
GO

-- Drop HelloWorldMessage if the message type exists.

IF EXISTS (SELECT *
           FROM sys.service_message_types
           WHERE name = 'HelloWorldMessage')
BEGIN
    DROP MESSAGE TYPE HelloWorldMessage ;
END ;
GO


-- Drop InitiatorQueue if the queue exists.

IF OBJECT_ID('[dbo].[InitiatorQueue]') IS NOT NULL AND
   EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID('[dbo].[InitiatorQueue]')
            AND type = 'SQ')
BEGIN
    DROP QUEUE [dbo].[InitiatorQueue] ;
END ;
GO

-- Drop TargetQueue if the queue exists.

IF OBJECT_ID('[dbo].[TargetQueue]') IS NOT NULL AND
   EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID('[dbo].[TargetQueue]')
            AND type = 'SQ')
BEGIN
    DROP QUEUE [dbo].[TargetQueue] ;
END ;
GO
--------------------------------------------------------------------

--------------------------------------------------------------------
-- Create objects for the sample.
--------------------------------------------------------------------

-- Create the message type. This message type accepts any
-- well-formed XML document.

CREATE MESSAGE TYPE HelloWorldMessage
    VALIDATION = WELL_FORMED_XML ;
GO

-- Create the contract. This contract allows either
-- participant in the conversation to send a
-- HelloWorldMessage.

CREATE CONTRACT HelloWorldContract
    ( HelloWorldMessage SENT BY INITIATOR);
GO

-- Create the queue for the target service.

CREATE QUEUE [dbo].[TargetQueue] ;
GO

-- Create the queue for the initiator service.

CREATE QUEUE [dbo].[InitiatorQueue] ;
GO

-- Create the initiator service. Notice that the
-- create statement for the service only includes
-- the contracts that the service is a target for.
-- In this case, the service only initiates conversations,
-- so the service does not specify any contract in
-- the service definition.

CREATE SERVICE InitiatorService
    ON QUEUE [dbo].[InitiatorQueue];
GO


-- Create the target service. Because this service
-- can be the target for conversations that follow
-- the HelloWorldContract, the service specifies
-- the contract in the service definition.

CREATE SERVICE TargetService
    ON QUEUE [dbo].[TargetQueue]
    (HelloWorldContract);
GO

---------------------------------------------------------------
-- SB Send

--------------------------------------------------------------------
-- Summary: SendMessage script for Service Broker
--          HelloWorld sample.
-- 
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------

USE AdventureWorks ;
GO


-- Begin a transaction.

BEGIN TRANSACTION ; 
GO

-- Create the message.

DECLARE @message XML ;
SET @message = N'<message>Hello, World!</message>' ;

-- Declare a variable to hold the conversation
-- handle.

DECLARE @conversationHandle UNIQUEIDENTIFIER ;

-- Begin the dialog.

BEGIN DIALOG CONVERSATION @conversationHandle
    FROM SERVICE InitiatorService
    TO SERVICE 'TargetService'
    ON CONTRACT HelloWorldContract ;

-- Send the message on the dialog.

SEND ON CONVERSATION @conversationHandle
  MESSAGE TYPE HelloWorldMessage
  (@message) ;

-- End the conversation.

END CONVERSATION @conversationHandle ;

GO

-- Commit the transaction. Service Broker
-- sends the message to the destination
-- service only when the transaction commits.

COMMIT TRANSACTION ;
GO


-- Show the messages in the target queue. Notice that target queue
-- contains both a HelloWorldMessage and a message of type
-- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.
SELECT * FROM [dbo].[TargetQueue]

-- Cast the message body as a string so we can see what it contains
/*
SELECT CAST(message_body as nvarchar(MAX)) from [dbo].[TargetQueue]
GO
*/

---------------------------------------------------------------
-- SB Receive

--------------------------------------------------------------------
-- Summary: RecievesMessage script for Service Broker
--          HelloWorld sample.
-- 
--------------------------------------------------------------------
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation. All Rights reserved.
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--------------------------------------------------------------------

USE AdventureWorks ;
GO

-- Process all conversation groups.
WHILE (1 = 1)
BEGIN

DECLARE @conversation_handle UNIQUEIDENTIFIER,
        @conversation_group_id  UNIQUEIDENTIFIER,
        @message_body XML,
        @message_type_name NVARCHAR(128);

BEGIN TRANSACTION ;

-- Get next conversation group.

WAITFOR(
   GET CONVERSATION GROUP @conversation_group_id FROM [dbo].[TargetQueue]),
   TIMEOUT 500 ;

-- If there are no more conversation groups, roll back the
-- transaction and break out of the outermost WHILE loop.

IF @conversation_group_id IS NULL
BEGIN
    ROLLBACK TRANSACTION ;
    BREAK ;
END ;

    -- Process all messages in the conversation group. Notice
    -- that all processing occurs in the same transaction.

    WHILE 1 = 1
    BEGIN

        -- Receive the next message for the conversation group.
        -- Notice that the receive statement includes a WHERE
        -- clause to ensure that the messages recieved belong to
        -- the same conversation group.

        RECEIVE
           TOP(1)
           @conversation_handle = conversation_handle,
           @message_type_name = message_type_name,
           @message_body =
           CASE
              WHEN validation = 'X' THEN CAST(message_body AS XML)
              ELSE CAST(N'<none/>' AS XML)
          END
        FROM [dbo].[TargetQueue]
        WHERE conversation_group_id = @conversation_group_id ;

        -- If there are no more messages, or an error occured,
        -- stop processing this conversation group.

        IF @@ROWCOUNT = 0 OR @@ERROR <> 0 BREAK;

        -- Show the information received.

        SELECT 'Conversation Group Id' = @conversation_group_id,
               'Conversation Handle' = @conversation_handle,
               'Message Type Name' = @message_type_name,
               'Message Body' = @message_body ;


        -- If the message_type_name indicates that the message is an error
        -- or an end dialog message, end the conversation.

        IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
           OR @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        BEGIN
           END CONVERSATION @conversation_handle ;
        END ;

    END; -- Process all messages in conversation group.

   -- Commit the receive statements and the end conversation statement. 

   COMMIT TRANSACTION ;

END ; -- Process all conversation groups.

---------------------------------------------------------------
-- Query Notifications - INSERT

INSERT Production.ProductReview(
 ProductID,ReviewerName,ReviewDate,EmailAddress,Rating,Comments)
VALUES ('937','BillG',GetDate(),'billg@microsoft.com',5,
 'Very satisfied. These pedals are fantastic!')
